home *** CD-ROM | disk | FTP | other *** search
- Sub AddFormulasToComments()
- Application.ScreenUpdating = False
- Dim CommentRange As Range, TargetCell As Range
- 'skip over errors caused by trying to delete comments in cells with no comments
- On Error Resume Next
- 'If the whole worksheet is selected, limit action to the used range.
- If Selection.Address = Cells.Address Then
- Set CommentRange = Range(ActiveSheet.UsedRange.Address)
- Else
- Set CommentRange = Range(Selection.Address)
- End If
- 'If the cell contains a formula, turn it into a comment.
- For Each TargetCell In CommentRange
- With TargetCell
- 'check whether the cell has a formula
- If Left(.Formula, 1) = "=" Then
- 'delete any existing comment
- .Comment.Delete
- 'add a new comment
- .AddComment
- 'copy the formula into the comment box
- .Comment.Text Text:=.Formula
- 'display the comment
- .Comment.Visible = True
- With .Comment.Shape
- 'automatically resizes the comment
- .TextFrame.AutoSize = True
- 'position the comment adjacent to its cell
- If TargetCell.Column < 254 Then.IncrementLeft -11.25
- If TargetCell.Row <> 1 Then .IncrementTop 8.25
- End With
- End If
- End With
- Next
- Application.ScreenUpdating = True
- End Sub
-
-